/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package INTROSE_MP;
import com.mysql.jdbc.PreparedStatement;
import javax.swing.UnsupportedLookAndFeelException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.swing.UIManager;


/**
 *
 * @author Paolo
 */
public class Main {
public static Connection conn;
 
    private static Statement stmt;
    private static PreparedStatement pstmt;
    private static ResultSet rset;
	
	public static String url = "jdbc:odbc:INTROSE MP", user = "root", pass = "";
           private static String basicURL="jdbc:mysql://127.0.0.1/introse";
	
    /**
     * @param args the command line arguments
     */
        public static void addProduct(String prodname, String size, int price, int stock)
    {
        System.out.println(prodname + " " + size + " " + price + " "+ stock);
        try {
            if(stock <= 50){
                pstmt = (com.mysql.jdbc.PreparedStatement)conn.prepareStatement("INSERT INTO introse.product (product_name, size, price, quantity, item_status) VALUES ('" + prodname + "', '" + size + "', " + price + ", " + stock + ", 'Critical')");
                pstmt.executeUpdate();
                                
            }
            else{
                pstmt = (com.mysql.jdbc.PreparedStatement)conn.prepareStatement("INSERT INTO introse.product (product_name, size, price, quantity, item_status) VALUES ('" + prodname + "', '" + size + "', " + price + ", " + stock + ", 'Stocked')");
                pstmt.executeUpdate();
            }
            
                               
        } catch (SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println(ex.getMessage());
        }
    }
           
           
        public static void connect()
    {  
          try {
                   Class.forName("com.mysql.jdbc.Driver");
                   conn = DriverManager.getConnection(basicURL, user, pass);
          }catch(Exception ex)
          {
          }
    }

     public static ResultSet doSelect(String sql)
    {
    	ResultSet rSet = null;
    	Statement st = null;
    	connect();
		try {st = (Statement)conn.createStatement();}
		catch (SQLException e) {e.printStackTrace();}
    	
		try {rSet = st.executeQuery(sql);}
		catch (SQLException e) {e.printStackTrace();}
		
		return rSet;
    }
    
    //Performs an SQL Data Modification Statement
    public static void doUpdate(String sql)
    {
    	Statement st = null;
    	
		try {st = conn.createStatement();}
		catch (SQLException e) {e.printStackTrace();}
    	
		try {st.executeUpdate(sql);}
		catch (SQLException e) {e.printStackTrace();}
		
		/*try {conn.commit();} 
		catch (SQLException e1) {e1.printStackTrace();}*/
    }
    
    public static void saveOrder(int ordercode, int clientID, String status, int productCode, int stock, int quantity)
    {
        try {
            Date d = new Date(System.currentTimeMillis());
            pstmt = (com.mysql.jdbc.PreparedStatement)conn.prepareStatement("INSERT INTO introse.order (ordercode,clientID,dateordered,status,productcode,quantity) values (?,?,?,?,?,?)");
            pstmt.setInt(1,ordercode);
            pstmt.setInt(2,clientID);
            pstmt.setDate(3, d);
            pstmt.setString(4, status);
            pstmt.setInt(5, productCode);
            pstmt.setInt(6, quantity);
                    
            pstmt.executeUpdate();
            alterQuantity(productCode, stock-quantity);
                   
        } catch (SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void alterQuantity(int productcode, int quantity){
        try {
            pstmt = (com.mysql.jdbc.PreparedStatement)conn.prepareStatement("UPDATE introse.product SET quantity=? where productcode=?");
            pstmt.setInt(1,quantity);
            pstmt.setInt(2, productcode);
            pstmt.executeUpdate();
            
        } catch (SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        
    }
     
    
     public static int getCurrentOrderCode(){
        int i = 0;
        try {
            pstmt = (com.mysql.jdbc.PreparedStatement)conn.prepareStatement("SELECT ordercode FROM introse.order");
            rset = pstmt.executeQuery();
            while(rset.next()) {
                i = rset.getInt("ordercode");
            }
            
            
            
        } catch (SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }
        if(i==0)
            return 1000;
        
        return i;
     }
     
       public static void updateStatus(String status, int ordercode)
        {
            try {
            pstmt = (com.mysql.jdbc.PreparedStatement)conn.prepareStatement("UPDATE introse.order SET status=? where ordercode=?");
            pstmt.setString(1,status);
            pstmt.setInt(2, ordercode);
            pstmt.executeUpdate();
            
        } catch (SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }
        }
      public static int getStock(int productcode){
       int x=0;
          try {
            pstmt = (com.mysql.jdbc.PreparedStatement)conn.prepareStatement("SELECT quantity FROM introse.product");
            rset = pstmt.executeQuery();
            while(rset.next()) {
                x = rset.getInt("quantity");
                    }
              } 
        catch (SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }
        return x;
      }
     
      public static boolean checkID(int clientID){
            int y=0;
           
            
           try {
            pstmt = (com.mysql.jdbc.PreparedStatement)conn.prepareStatement("SELECT clientID FROM introse.client where clientID=?");
            pstmt.setInt(1, clientID);
            rset = pstmt.executeQuery();
            while(rset.next()) {
                
                return true;
                    }
            
              } 
        catch (SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }
      
     return false;
}
   
    
    public static void main(String[] args) {
        // TODO code application logic here
        
        String laf = UIManager.getSystemLookAndFeelClassName();
        try {
            UIManager.setLookAndFeel(laf);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        } catch (UnsupportedLookAndFeelException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        LoginWindow l = new LoginWindow();
        l.setVisible(true);
        l.setLocationRelativeTo(null);


    }

}
